with dwd_yl_oms_order_third_ext_base_dt as (
    select 
        t1.post_site_name     as business_name                   --品牌
       ,t1.post_site_code                                        --门店编码
       ,t3.name               as post_site_name                  --门店name
       ,t1.post_site_address                                     --订单id
       ,t1.order_id                                              --门店地址
    from   jms_dwd.dwd_yl_oms_order_third_ext_base_dt t1 
    left join jms_dim.dim_tab_agent_point_base t3 on  if(t1.post_site_code is null,t1.order_id,t1.post_site_code) =t3.courier_station_code
    where dt between date_add('{{ execution_date  | cst_ds }}', -7) and '{{ execution_date  | cst_ds }}'  
    group by 
        t1.post_site_name     
       ,t1.post_site_code                              
       ,t3.name               
       ,t1.post_site_address                                   
       ,t1.order_id        
)
insert overwrite table jms_dm.dm_end_sender_report_detail_dt partition(dt)
select
    t.input_time                                             --订单录入时间
   ,t.waybill_id                                             --运单号
   ,t.order_source_code                                      --订单来源code
   ,t.order_source_name                                      --订单来源名称
   ,t.order_status_code                                      --订单状态code
   ,t.real_pick_network_code                                 --实际取件网点code
   ,t.real_pick_network_name                                 --实际取件网点名称
   ,t.pick_time                                              --取件时间
   ,t.best_pick_time_end                                     --最佳取件结束时间
   ,t.cancel_time                                            --订单取消时间
   ,t.cancel_reason_code                                     --订单取消原因编号
   ,t.cancel_reason                                          --取消订单原因
   ,t.business_name      as business_name                    --品牌
   ,t.post_site_code                                         --门店编码
   ,t.post_site_name     as post_site_name                   --门店name
   ,t.post_site_address                                      --门店地址
   ,t.customer_code                                          --客户编号code
   ,t.customer_name                                          --客户编号名称
   ,t.pick_regional_code      as pick_regional_code          --取件_大区code
   ,t.pick_regional_name      as pick_regional_name          --取件_大区name
   ,t.pick_agent_code         as pick_agent_code             --取件_代理区编码
   ,t.pick_agent_name         as pick_agent_name             --取件_代理区名称
   ,t.pick_fran_code          as pick_fran_code              --取件_加盟商
   ,t.pick_fran_name          as pick_fran_name              --取件_加盟商
   ,t.pick_network_code                                      --取件网点code
   ,t.pick_network_name                                      --取件网点名称
   ,t.sender_province_id                                     --寄件省份id
   ,t.sender_province_name                                   --寄件省份
   ,t.sender_city_id                                         --寄件城市id
   ,t.sender_city_name                                       --寄件城市
   ,t.sender_area_id                                         --寄件区域id
   ,t.sender_area_name                                       --寄件区域
   ,t.sender_detailed_address                                --寄件详细地址
   ,t.receiver_province_id                                   --收件省份id
   ,t.receiver_province_name                                 --收件省份
   ,t.receiver_city_id                                       --收件城市id
   ,t.receiver_city_name                                     --收件城市
   ,t.receiver_area_id                                       --收件区域id
   ,t.receiver_area_name                                     --收件区域
   ,t.receiver_detailed_address                              --收件详细地址
   ,t.category_id
   ,t.date_time                                              --分区
   ,t.dt                                                     --分区
from (
    select
        t.input_time                                             --订单录入时间
       ,t.waybill_id                                             --运单号
       ,case when  t.order_source_name <> '物流来了'  then t.order_source_code
             when  t.order_source_name =  '物流来了' and customer_order_id like 'wlExpwxA%' then t.order_source_code
             else null end order_source_code             --订单来源code
       ,t.order_source_name                                      --订单来源名称
       ,t.order_status_code                                      --订单状态code
       ,t.real_pick_network_code                                 --实际取件网点code
       ,t.real_pick_network_name                                 --实际取件网点名称
       ,t.pick_time                                              --取件时间
       ,t.pick_end_time as best_pick_time_end                    --最佳取件结束时间
       ,t.cancel_time                                            --订单取消时间
       ,t.cancel_reason_code                                     --订单取消原因编号
       ,t.cancel_reason                                          --取消订单原因
       ,t1.business_name      as business_name                   --品牌
       ,t1.post_site_code                                        --门店编码
       ,t1.post_site_name     as post_site_name                  --门店name
       ,t1.post_site_address                                     --门店地址
       ,case when  t.order_source_name  not in ('快递鸟散件','快宝散件','快递100散件','快递100商家件','白鸽惠递') then t.customer_code
             when  t.order_source_name  ='快递鸟散件'     and t.customer_code ='J0086657868'  then t.customer_code
             when  t.order_source_name  ='快宝散件'       and t.customer_code ='J0086193488'  then t.customer_code
             when  t.order_source_name  ='快递100散件'    and t.customer_code ='J0086119071'  then t.customer_code
             when  t.order_source_name  ='快递100商家件'  and t.customer_code ='J0086117925'  then t.customer_code
             when  t.order_source_name  ='白鸽惠递'       and t.customer_code ='J0086766708'  then t.customer_code
             else null end as        customer_code --客户编号code


       ,t.customer_name --客户编号名称

       ,case when t2.virt_code is null or t2.virt_code ='' then t2.agent_code else t2.virt_code end  as pick_regional_code          --取件_大区code
       ,case when t2.virt_code is null or t2.virt_code ='' then t2.agent_name else t2.virt_name end  as pick_regional_name          --取件_大区name
       ,t2.agent_code         as pick_agent_code                 --取件_代理区编码
       ,t2.agent_name         as pick_agent_name                 --取件_代理区名称
       ,t2.fran_code          as pick_fran_code                  --取件_加盟商
       ,t2.fran_name          as pick_fran_name                  --取件_加盟商
       ,t.pick_network_code                                      --取件网点code
       ,t.pick_network_name                                      --取件网点名称
       ,t.sender_province_id                                     --寄件省份id
       ,t.sender_province_name                                   --寄件省份
       ,t.sender_city_id                                         --寄件城市id
       ,t.sender_city_name                                       --寄件城市
       ,t.sender_area_id                                         --寄件区域id
       ,t.sender_area_name                                       --寄件区域
       ,t.sender_detailed_address                                --寄件详细地址
       ,t.receiver_province_id                                   --收件省份id
       ,t.receiver_province_name                                 --收件省份
       ,t.receiver_city_id                                       --收件城市id
       ,t.receiver_city_name                                     --收件城市
       ,t.receiver_area_id                                       --收件区域id
       ,t.receiver_area_name                                     --收件区域
       ,t.receiver_detailed_address                              --收件详细地址
       ,dict.category_id                                         --84门店散单,85线上散单
       ,to_date(t.input_time) as date_time  --分区
       ,to_date(t.input_time) as dt         --分区
       ,row_number()over(partition by t.waybill_id order by t.input_time desc ) rn
       ,case when dict.name<>'桃花岛' and  dict.name<>'七星潭' and  dict.name<>'拼多多'          then 1
             when dict.name='桃花岛'  and  ct.attribute_id=1353  then 1
             when dict.name='七星潭'  and  ct.attribute_id=1352  then 1
             else 0 end as flg
    from jms_dwd.dwd_yl_oms_oms_order_incre_dt t
    left join jms_dim.dim_network_whole_massage t2 on t.pick_network_code=t2.code
    left join dwd_yl_oms_order_third_ext_base_dt t1 on t.id=t1.order_id
    left join jms_dim.dim_yl_lmdm_sys_dictionary_base dict on  dict.category_id in (84,85) and dict.is_enable=1
               and case when t.order_source_name in ('桃花岛','拼多多')  then '桃花岛'  else t.order_source_name end  = dict.name
    left join jms_dim.dim_lmdm_sys_customer ct on t.customer_code=ct.code
    where t.dt between date_add('{{ execution_date  | cst_ds }}', -7) and '{{ execution_date  | cst_ds }}'
         and to_date(t.input_time) between date_add('{{ execution_date  | cst_ds }}', -4) and '{{ execution_date  | cst_ds }}'
         and dict.category_id   is not null
) t where rn =1 and flg=1    and order_source_code is not null

 distribute by dt, abs(hash(waybill_id)) % 50
;
